using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Colorprj.Masters;
using System.Data.Common;
using Colorprj.Common;
namespace Colorprj.Invoice
{
    class InvoiceManager
    {


        public int GetNextInvNo()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "GetNextInvNo";
            DbCommand GetVNoCommand = db.GetStoredProcCommand(sqlCommand);
           // db.AddInParameter(GetVNoCommand, "vchType", DbType.String, VType);
            int VNO = Convert.ToInt16(db.ExecuteScalar(GetVNoCommand));
            return VNO;

        }

        public bool AddInvoice(InvoiceHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddInvoiceHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "InvoiceNo", DbType.String, phEntity.InvoiceNo);
            db.AddInParameter(creditCommand, "InvoiceDate", DbType.DateTime, phEntity.InvoiceDate);
            db.AddInParameter(creditCommand, "DueDate", DbType.DateTime, phEntity.DueDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);
            db.AddInParameter(creditCommand, "OrderDate", DbType.DateTime, phEntity.OrderDate );
            db.AddInParameter(creditCommand, "DespachedDate", DbType.DateTime, phEntity.DespachedDate );
            db.AddInParameter(creditCommand, "DespachedThrough", DbType.String, phEntity.DespachedThrough );
            db.AddInParameter(creditCommand, "Destination", DbType.String, phEntity.Destination);
            db.AddInParameter(creditCommand, "VatTaxPer", DbType.Int16 , phEntity.VatTaxPer );
            db.AddInParameter(creditCommand, "VatTaxAmount", DbType.Double, phEntity.VatTaxAmount );
            db.AddInParameter(creditCommand, "Mop", DbType.String, phEntity.Mop);
            db.AddInParameter(creditCommand, "@InvAmount", DbType.String, phEntity.InvAmount);
            db.AddInParameter(creditCommand, "@ChallanNos", DbType.String, phEntity.ChallanNos );
            db.AddInParameter(creditCommand, "CstPer", DbType.Int16, phEntity.CstPer);
            db.AddInParameter(creditCommand, "CstAmount", DbType.Double, phEntity.CstAmount);
            db.AddInParameter(creditCommand, "SalesManId", DbType.Int32, phEntity.SalesManId );

            List<InvoiceDetailEntity> InvoiceDetailList = phEntity.InvoiceDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int InvoiceHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddInvoiceDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (InvoiceDetailEntity Invoice in InvoiceDetailList)
                    {
                        DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(debitCommand, "InvoiceId", DbType.Int32, InvoiceHeadId);
                        //db.AddInParameter(debitCommand, "ProductId", DbType.Int32, Invoice.ProductId);
                        db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, Invoice.SubproductId);
                        db.AddInParameter(debitCommand, "Qty", DbType.Int32, Invoice.Qty);
                        db.AddInParameter(debitCommand, "Rate", DbType.Decimal, Invoice.Rate);
                        db.AddInParameter(debitCommand, "Amount", DbType.Decimal, Invoice.Amount);
                        if (!string.IsNullOrEmpty(phEntity.ChallanNos))
                        {
                            db.AddInParameter(debitCommand, "@FromChallan", DbType.Boolean, true);
                        }
                        else {
                            db.AddInParameter(debitCommand, "@FromChallan", DbType.Boolean, false);
                        }

                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    if (!string.IsNullOrEmpty(phEntity.ChallanNos))
                    {
                        sqlCommand = "UpdChallanWithInv";
                        DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(debitCommand, "InvoiceId", DbType.Int32, InvoiceHeadId);
                        db.AddInParameter(debitCommand, "InvoiceNo", DbType.String, phEntity.InvoiceNo);
                        db.AddInParameter(debitCommand, "ChallanIds", DbType.String, phEntity.ChallanNos);
                        db.ExecuteNonQuery(debitCommand, transaction);
                    }

                    
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception ex)
                {
                    // Rollback transaction
                    transaction.Rollback();
                    throw new Exception(ex.Message) ; 
                    
                }
                connection.Close();

                return result;
            }
        }


        public DataSet getAllInvoices()
        {
        
        // List<CodeValuePair> Invoicelist= new List<CodeValuePair>();

             // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            string sqlCommand = "Select InvoiceID , InvoiceNo from invoiceHead ";
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

            DataSet productsDataSet = null;

            productsDataSet = db.ExecuteDataSet(dbCommand);

            return productsDataSet;

            //return  Invoicelist;
        }


        public SubProduct GetSubProductById(int Id)
        {
            SubProductManager spm = new SubProductManager();

            return spm.GetSubProductById(Id);
        }

        public bool UpdInvoice(InvoiceHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdInvoiceHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);

            db.AddInParameter(creditCommand, "InvoiceId", DbType.Int32, phEntity.InvoiceHeadId);
            db.AddInParameter(creditCommand, "InvoiceNo", DbType.String, phEntity.InvoiceNo);
            db.AddInParameter(creditCommand, "InvoiceDate", DbType.DateTime, phEntity.InvoiceDate);
            db.AddInParameter(creditCommand, "BuyerId", DbType.Int32, phEntity.BuyerId);
            db.AddInParameter(creditCommand, "TotalAmount", DbType.Double, phEntity.TotalAmount);
            db.AddInParameter(creditCommand, "InvAmount", DbType.Double, phEntity.InvAmount);
            db.AddInParameter(creditCommand, "OrderDate", DbType.DateTime, phEntity.OrderDate);
            db.AddInParameter(creditCommand, "DespachedDate", DbType.DateTime, phEntity.DespachedDate);
            db.AddInParameter(creditCommand, "DespachedThrough", DbType.String, phEntity.DespachedThrough);
            db.AddInParameter(creditCommand, "Destination", DbType.String, phEntity.Destination);
            db.AddInParameter(creditCommand, "VatTaxPer", DbType.Int16, phEntity.VatTaxPer);
            db.AddInParameter(creditCommand, "VatTaxAmount", DbType.Double, phEntity.VatTaxAmount);
            db.AddInParameter(creditCommand, "Mop", DbType.String, phEntity.Mop);
            db.AddInParameter(creditCommand, "@ChallanNos", DbType.String, phEntity.ChallanNos);
       


            List<InvoiceDetailEntity> InvoiceDetailList = phEntity.InvoiceDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int InvoiceHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddInvoiceDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (InvoiceDetailEntity Invoice in InvoiceDetailList)
                    {
                        DbCommand debitCommand;
                        if (Invoice.InvoiceDetailId == 0)
                        {
                            debitCommand = db.GetStoredProcCommand(sqlCommand);
                            db.AddInParameter(debitCommand, "@FromChallan", DbType.Boolean, false);
                        }
                        else
                        {
                            if (Invoice.Qty == 0) { debitCommand = db.GetStoredProcCommand("DelInvoiceDetail"); }

                            else
                            {
                                debitCommand = db.GetStoredProcCommand("UpdInvoiceDetail");
                                db.AddInParameter(debitCommand, "OldQty", DbType.Decimal, Invoice.OldQty);
                            }
                            db.AddInParameter(debitCommand, "InvoiceDetailId", DbType.Decimal, Invoice.InvoiceDetailId);
                        }
                        if (Invoice.Qty != 0)
                        {
                            db.AddInParameter(debitCommand, "InvoiceId", DbType.Int32, phEntity.InvoiceHeadId);
                            //db.AddInParameter(debitCommand, "ProductId", DbType.Int32, Invoice.ProductId);
                            db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, Invoice.SubproductId);
                            db.AddInParameter(debitCommand, "Qty", DbType.Int32, Invoice.Qty);
                            db.AddInParameter(debitCommand, "Rate", DbType.Decimal, Invoice.Rate);
                            db.AddInParameter(debitCommand, "Amount", DbType.Decimal, Invoice.Amount);
                           
                        }
                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception ex)
                {
                    // Rollback transaction
                    
                    transaction.Rollback();
                    throw new Exception(ex.Message) ; 
                }
                connection.Close();

                return result;
            }
        }

        public InvoiceHeadEntity GetInvoice(int InvoiceId)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelInvoiceHeadDet";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "InvoiceHeadId", DbType.Int32, InvoiceId);

            DataSet InvoicechaseDataSet = db.ExecuteDataSet(creditCommand);
            InvoiceHeadEntity phEntity = new InvoiceHeadEntity();
            List<InvoiceDetailEntity> InvoiceDetailList = new List<InvoiceDetailEntity>();
            foreach (DataRow dr in InvoicechaseDataSet.Tables[0].Rows)
            {
                phEntity.BuyerId = Convert.ToInt16(dr["BuyerId"]);
                phEntity.InvoiceHeadId = Convert.ToInt16(dr["InvoiceId"]);
                phEntity.InvoiceNo = dr["InvoiceNo"].ToString();
                phEntity.InvoiceDate = Convert.ToDateTime(dr["InvoiceDate"]);
                phEntity.DueDate = Convert.ToDateTime(dr["DueDate"]);
                phEntity.OrderDate = Convert.ToDateTime(dr["OrderDate"]);
                phEntity.DespachedDate = Convert.ToDateTime(dr["DespachedDate"]);
                phEntity.Mop  = dr["mop"].ToString() ;
                phEntity.DespachedThrough = dr["DespachedThrough"].ToString() ;
                phEntity.Destination = dr["Destination"].ToString();
                phEntity.TotalAmount = Convert.ToDouble(dr["TotalAmount"]);
                phEntity.VatTaxAmount = Convert.ToDouble(dr["VatTaxAmount"]);
                phEntity.VatTaxPer = Convert.ToInt16(dr["VatTaxPer"]);
                phEntity.ChallanNos  = dr["ChallanNos"].ToString() ;
                phEntity.SalesManId = Convert.ToInt16(dr["SalesManId"]);

                InvoiceDetailEntity pdEntity = new InvoiceDetailEntity();
                //pdEntity.ProductId = Convert.ToInt16(dr["ProductId"]);
                pdEntity.SubproductId = Convert.ToInt16(dr["SubproductId"]);
                pdEntity.Qty = Convert.ToInt16(dr["Qty"]);
                pdEntity.Rate = Convert.ToDecimal (dr["Rate"]);
                pdEntity.Amount = Convert.ToDecimal(dr["Amount"]);
                pdEntity.InvoiceDetailId = Convert.ToInt16(dr["InvoiceDetailId"]);
                InvoiceDetailList.Add(pdEntity);
            }
            phEntity.InvoiceDetailList = InvoiceDetailList;
            return phEntity;
        }


        public List<InvoiceDetailEntity> GetChallanProduct(string challanIds)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelChallanProduct";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "filter", DbType.String, challanIds);

            DataSet InvoicechaseDataSet = db.ExecuteDataSet(creditCommand);
            
            List<InvoiceDetailEntity> InvoiceDetailList = new List<InvoiceDetailEntity>();
            foreach (DataRow dr in InvoicechaseDataSet.Tables[0].Rows)
            {
               

                InvoiceDetailEntity pdEntity = new InvoiceDetailEntity();
                pdEntity.ProductId = Convert.ToInt16(dr["ProductId"]);
                pdEntity.SubproductId = Convert.ToInt16(dr["SubproductId"]);
                pdEntity.Qty = Convert.ToInt16(dr["Qty"]);
                pdEntity.Rate = Convert.ToDecimal(dr["Rate"]);
                pdEntity.Amount = Convert.ToDecimal(dr["Amount"]);
                //pdEntity.InvoiceDetailId = Convert.ToInt16(dr["InvoiceDetailId"]);
                InvoiceDetailList.Add(pdEntity);
            }
            
            return InvoiceDetailList;
        }

    }
}
